<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.math.BigDecimal,java.util.Date" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@page import="java.text.SimpleDateFormat"%><html>
<head>
<title></title>
<LINK href="/hr_Jsp/report.css" rel=stylesheet>
<style type="text/css">
.45percentage {
	float: right;
	width: 100%;
	position: relative;
}

#50percentage {
	float: left;
	width: 100%;
	position: relative;
	clear: both;
}
#left {
	float: left;
	width: 48%;
	position: relative;
}
#right {
	float: left;
	position: relative;
	width: 48%;
	margin-left: 5px;
}
</style>
</head>
<body>
<%
    String excel = request.getParameter("excel");
	String year = request.getParameter("yearMonth");
	if (excel != null && !excel.equals("")) {
		response.setHeader("Content-disposition","attachment; filename=leave.xls");
	}
	if (year == null) {
		Date defaultDate = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
		String date = format.format(defaultDate);
		year = date;
	}
	DataSetTool DST = new DataSetTool();
	String yearSql = "select *,leavNo*1.0/allEmploye as turnover from (select substring(CONVERT(varchar(100), lastjobdate, 23),1,7) as date,case when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '01' then '一月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '02' then '二月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '03' then '三月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '04' then '四月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '05' then '五月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '06' then '六月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '07' then '七月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '08' then '八月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '09' then '九月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '10' then '十月' when substring(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),6,7) = '11' then '十一月' else '十二月' end as months,(select cast(round(avg(sun*1.00),0) as int) from (select (select count(*) from bcode_job where isnull(substring(CONVERT(varchar(100), lastjobdate, 23),1,7),'2050-08') > substring(CONVERT(varchar(100), job.lastjobdate, 23),1,7) and substring(CONVERT(varchar(100), entrydate, 23),1,7) <= substring(CONVERT(varchar(100), substring(CONVERT(varchar(100), job.lastjobdate, 23),1,7), 23),1,7) and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) > substring(CONVERT(varchar(100), job.lastjobdate, 23), 1, 7) and bcode = bcode_job.bcode)) as sun from bcode_job job where substring(CONVERT(varchar(100), lastjobdate, 23),1,4) = substring(substring(CONVERT(varchar(100), a1.lastjobdate, 23),1,7),1,4) and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) <= substring(CONVERT(varchar(100), a1.lastjobdate, 23),1,7) group by substring(CONVERT(varchar(100), lastjobdate, 23),1,7)) as a ) as allEmploye,(select count(*) from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) = substring(CONVERT(varchar(100), a1.lastjobdate, 23),1,7) and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) > substring(CONVERT(varchar(100), a1.lastjobdate, 23),1,7) and bcode = bcode_job.bcode)) as leavNo,(select count(*) from bcode_job where substring(CONVERT(varchar(100), entrydate, 23),1,7) = substring(CONVERT(varchar(100), a1.lastjobdate, 23),1,7) and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) > substring(CONVERT(varchar(100), a1.lastjobdate, 23),1,7) and bcode = bcode_job.bcode)) as entryNo from bcode_job a1 where substring(CONVERT(varchar(100), lastjobdate, 23),1,4) = '" + year.substring(0, 4) + "' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) <= '" + year + "' group by substring(CONVERT(varchar(100), lastjobdate, 23),1,7)) monthStatistics";
	String depSql = "select name,num,isnull(leavNo.num1,0) as leavNo,isnull(entryNo.num2,0) as entryNo,isnull(leavNo.num1,0)*1.00/allNo.num as churn,isnull(leavNo.num1,0)*1.00/_sum as proportion from (select systemid,cast(round(avg(sun*1.00),0) as int) as num from (select systemid,(select count(*) from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > CONVERT(varchar(7), date, 120) and CONVERT(varchar(7), entrydate, 120) <= CONVERT(varchar(7), date, 120) and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(7),ChangeDate,120) > CONVERT(varchar(7), date, 120) and bcode = a.bcode) and b.systemid like allSystemId.systemid + '%') as sun from (select id as systemid from systeminfo where len(id) = 4) as allSystemId inner join (select CONVERT(varchar(7), lastjobdate, 120) as date from bcode_job where CONVERT(varchar(4), lastjobdate, 120) = '" + year.substring(0, 4) + "' and CONVERT(varchar(7), lastjobdate, 120) <= '" + year + "' group by CONVERT(varchar(7), lastjobdate, 120)) as allMonths on 1=1) as last group by systemid) as allNo left join (select substring(systemid,1,4) as systemid,count(systemid) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where employstatus='离职' and CONVERT(varchar(7), lastjobdate, 120) <= '" + year + "' and CONVERT(varchar(4), lastjobdate, 120) = '" + year.substring(0, 4) + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = allNo.systemid left join (select substring(systemid,1,4) as systemid,count(systemid) as num2 from bcode_job a inner join bcode b on b.bcode = a.bcode where CONVERT(varchar(7), entrydate, 120) <= '" + year + "' and CONVERT(varchar(4), entrydate, 120) = '" + year.substring(0, 4) + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and convert(varchar(4),ChangeDate,120) = '" + year.substring(0, 4) + "' and convert(varchar(7),ChangeDate,120) <= '" + year + "' and bcode = a.bcode) group by substring(systemid,1,4)) as entryNo on entryNo.systemid = allNo.systemid left join (select count(*) as _sum from bcode_job a inner join bcode b on b.bcode = a.bcode where employstatus='离职' and CONVERT(varchar(7), lastjobdate, 120) <= '" + year + "' and CONVERT(varchar(4), lastjobdate, 120) = '" + year.substring(0, 4) + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工') as allLeav on 1=1 inner join (select name,id from systeminfo) as systeminfo on systeminfo.id = allNo.systemid where allNo.num> 0";
	String reasonSql = "select leavereason,count(employlevel) as num from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) <= '" + year + "' and substring(CONVERT(varchar(100), lastjobdate, 23),1,4) = '" + year.substring(0, 4) + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' group by leavereason";
	String workExperienceSql = "select workExperience,count(*) as num from (select case when(0<=Work_experience and Work_experience<=0.3) then '0－3个月' when(0.3<Work_experience and Work_experience<=0.6) then '3－6个月' when(0.6<Work_experience and Work_experience<=1) then '6－12个月' when(1<Work_experience and Work_experience<=3) then '1－3年' when(3<Work_experience and Work_experience<=5) then '3－5年' else '5年以上' end as workExperience,case when(0<=Work_experience and Work_experience<=0.3) then 1 when(0.3<Work_experience and Work_experience<=0.6) then 2 when(0.6<Work_experience and Work_experience<=1) then 3 when(1<Work_experience and Work_experience<=3) then 4 when(3<Work_experience and Work_experience<=5) then 5 else 6 end as sort from bcode_job where employstatus='离职' and substring(CONVERT(varchar(100), lastjobdate, 23),1,7) <= '" + year + "' and substring(CONVERT(varchar(100), lastjobdate, 23),1,4) = '" + year.substring(0, 4) + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工') as a group by workExperience, sort order by sort";
	DataSet yearLeaving =DST.getDataSet(yearSql);
	DataSet depLeaving =DST.getDataSet(depSql);
	DataSet reasonLeaving =DST.getDataSet(reasonSql);
	DataSet workExperienceLeaving =DST.getDataSet(workExperienceSql);
	
	int sumAll = 0;  
	int sumLeav = 0;
	int sumEntry = 0;
%>
<%
	if (yearLeaving.getRowCount() == 0) {
%>
<%= year.substring(0, 4) %>年累计离职统计没有任何记录
<%
	} else {
%>
<div class="45percentage">
<%= year.substring(0, 4) %>-01月到<%= year %>月累计离职统计<br/>
<table width="100%" style="border-collapse: collapse" border="1"  cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr bgcolor="#e8e8e0" align="center">
    <td>月份</td>
    <td>平均人数</td>
    <td>离职人数</td>
    <td>流失率</td>
    <td>入职人数</td>
  </tr>
<%
for(int i=0; i<yearLeaving.getRowCount(); i++) {
	float f = Float.parseFloat(yearLeaving.getValue(i, "turnover")) * 100;
	BigDecimal r = new BigDecimal(f).setScale(2,BigDecimal.ROUND_HALF_UP);
%>
  <tr>
    <td><%= yearLeaving.getValue(i, "months") %></td>
    <td><%= yearLeaving.getValue(i, "allEmploye") %></td>
    <td><%= yearLeaving.getValue(i, "leavNo") %></td>
    <td><%= r + "%" %></td>
    <td><%= yearLeaving.getValue(i, "entryNo") %></td>
  </tr>
<%
}
%>
</table>
</div>
<br />
<div id="50percentage">
<%= year %>月部门累计离职统计<br/>
<table width="100%" style="border-collapse: collapse" border="1"  cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
    <tr bgcolor="#e8e8e0" align="center">
      <td>部门</td>
      <td>本月平均人数</td>
      <td>累计离职人数</td>
      <td>部门流失率</td>
      <td>占总离职人数比例</td>
      <td>入职人数</td>
    </tr>
<%
	for(int i=0; i<depLeaving.getRowCount(); i++) {
	float churn = Float.parseFloat(depLeaving.getValue(i, "churn")) * 100;
	float proportion = Float.parseFloat(depLeaving.getValue(i, "proportion")) * 100;
	BigDecimal r1 = new BigDecimal(churn).setScale(2,BigDecimal.ROUND_HALF_UP);
	BigDecimal r2 = new BigDecimal(proportion).setScale(2,BigDecimal.ROUND_HALF_UP);
	sumAll = sumAll + Integer.parseInt(depLeaving.getValue(i, "num"));
	sumLeav = sumLeav + Integer.parseInt(depLeaving.getValue(i, "leavNo"));
	sumEntry = sumEntry + Integer.parseInt(depLeaving.getValue(i, "entryNo"));
%>
    <tr>
      <td><%= depLeaving.getValue(i, "name") %></td>
      <td><%= depLeaving.getValue(i, "num") %></td>
      <td><%= depLeaving.getValue(i, "leavNo") %></td>
      <td><%= r1 + "%" %></td>
      <td><%= r2 + "%" %></td>
      <td><%= depLeaving.getValue(i, "entryNo") %></td>
    </tr>
<%
}
%>
	<tr>
      <td>总计</td>
      <td><%= sumAll %></td>
      <td><%= sumLeav %></td>
      <td></td>
      <td></td>
      <td><%= sumEntry %></td>
    </tr>
  </table>
</div>
<br />
<div class="45percentage">
<div id="left">
<%= year %>月离职原因累计<br/>
<table width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr bgcolor="#e8e8e0" align="center">
    <td>离职原因</td>
    <td>离职人数</td>
  </tr>
<%
	for(int i=0; i<reasonLeaving.getRowCount(); i++) {
%>
  <tr>
    <td><%= reasonLeaving.getValue(i, "leavereason") %></td>
    <td><%= reasonLeaving.getValue(i, "num") %></td>
  </tr>
<%
}
%>
	<tr>
    <td>合计</td>
    <td><%= sumLeav %></td>
  </tr>
</table>
</div>

<div id="right">
<%= year %>月离职人员工作年限累计<br/>
<table width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr bgcolor="#e8e8e0" align="center">
    <td>年限</td>
    <td>离职人数</td>
  </tr>
<%
	for(int i=0; i<workExperienceLeaving.getRowCount(); i++) {
%>
  <tr>
    <td><%= workExperienceLeaving.getValue(i, "workExperience") %></td>
    <td><%= workExperienceLeaving.getValue(i, "num") %></td>
  </tr>
<%
}
%>
	<tr>
    <td>合计</td>
    <td><%= sumLeav %></td>
  </tr>
</table>
</div>
</div>
<%
}
%>
</body>
</html>
